package com.iotechn.unimall.launcher.controller;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.iotechn.unimall.data.domain.PlantGoodsDO;
import com.iotechn.unimall.data.domain.ShopBiaozhunDO;
import com.iotechn.unimall.data.domain.ShopCailiaoDO;
import com.iotechn.unimall.data.domain.UserShopDO;
import com.iotechn.unimall.data.dto.plantgood.BiaozhunExcelDTO;
import com.iotechn.unimall.data.dto.plantgood.CailiaoExcelDTO;
import com.iotechn.unimall.data.mapper.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Controller
@RequestMapping("/excel")
@CrossOrigin
public class ExcelController {

    @Autowired
    private UserShopMapper userShopMapper;
    @Autowired
    private SpuMapper spuMapper;
    @Autowired
    private ShopBiaozhunMapper shopBiaozhunMapper;
    @Autowired
    private ShopCailiaoMapper shopCailiaoMapper;


    @GetMapping("/getAllBiaozhun")
    @ResponseBody
    public void getAllBiaozhun(
            HttpServletRequest request,
            HttpServletResponse response,
            @RequestParam(required = false, value = "shopId") Long shopId,
            @RequestParam(required = false, value = "startStr") String startStr,
            @RequestParam(required = false, value = "endStr") String endStr
            ){
        try {
        response.reset();
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setContentType("application/octet-stream;charset=UTF-8");
        //设置导出的文件名
        final String extraName = "项目完成进度汇总表.xls";
        StringBuffer fileName = new StringBuffer(extraName);
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode(fileName.toString(), "UTF-8"));
            Wrapper<ShopBiaozhunDO> wrapper = new EntityWrapper<ShopBiaozhunDO>();
            if(shopId!=null){
                wrapper.eq("shop_id",shopId);
            }
            if(org.apache.commons.lang3.StringUtils.isNotEmpty(startStr)){
                Date start=new Date();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                try {
                    start = dateFormat.parse(startStr);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                wrapper.ge("gmt_update",start);
            }
            if(org.apache.commons.lang3.StringUtils.isNotEmpty(endStr)){
                Date end=new Date();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                try {
                    end = dateFormat.parse(endStr);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                wrapper.le("gmt_update",end);
            }
            List<BiaozhunExcelDTO> plantGoodExcelDTOs = new ArrayList<>();

            List<ShopBiaozhunDO> shopBiaozhunDOS = shopBiaozhunMapper.selectList(wrapper);
            for (ShopBiaozhunDO shopBiaozhunDO: shopBiaozhunDOS
            ){
                BiaozhunExcelDTO dto = new BiaozhunExcelDTO();
                dto.setGongchengName(shopBiaozhunDO.getGongchengName());
                dto.setGongchengCode(shopBiaozhunDO.getGongchengCode());
                dto.setFinishTime(shopBiaozhunDO.getFinishTime());
                dto.setFinishValue(shopBiaozhunDO.getFinishValue());
                dto.setBunit(shopBiaozhunDO.getBunit());
                dto.setGongcheng_price(shopBiaozhunDO.getGongcheng_price());
                dto.setGongcheng_allprice(shopBiaozhunDO.getGongcheng_allprice());
                dto.setGongcheng_count(shopBiaozhunDO.getGongcheng_count()+"");
                dto.setGongchengXuhao(shopBiaozhunDO.getGongchengXuhao());
                dto.setGongchengPxuhao(shopBiaozhunDO.getGongchengPxuhao());
                dto.setShopName(shopBiaozhunDO.getShopName());
                dto.setDesc(shopBiaozhunDO.getDesc());
                 plantGoodExcelDTOs.add(dto);
            }

            //设置下载的响应头
            ExcelExportService excelExportService = new ExcelExportService();
            Workbook workbook = new HSSFWorkbook();
            ExportParams exportParams = new ExportParams();
            final String sheetName = startStr + " - " + endStr+"工程信息";
            StringBuffer title = new StringBuffer(sheetName);
            exportParams.setSheetName(sheetName);

            ExportParams userDistributionExportParams = new ExportParams();
            userDistributionExportParams.setTitle(title.toString());
            userDistributionExportParams.setSheetName(title.toString());
            excelExportService.createSheet(workbook, userDistributionExportParams, BiaozhunExcelDTO.class, plantGoodExcelDTOs);
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @PostMapping("/batchUpload")
    @ResponseBody
    public ExcelResponse batchUpload(
            HttpServletRequest request,
            HttpServletResponse response,
            @RequestParam("file") MultipartFile file) {
        String shopIdString = request.getParameter("shopId");
        Long shopId = null;
        if (shopIdString != null && !shopIdString.equals("undefined")) {
            shopId = Long.valueOf(shopIdString);
        } else {
            ExcelResponse excelResponse = new ExcelResponse();
            excelResponse.setErrno(0);
            excelResponse.setErrmsg("请选择对应项目");
            return excelResponse;
        }
        UserShopDO userShopDO = userShopMapper.selectById(shopId);
        ExcelResponse excelResponse = this.ImportExcel(file, userShopDO);
        if (excelResponse.getErrno() == 0) {
            //代表有异常
            return excelResponse;
        } else {

            List<ShopBiaozhunDO>  shopBiaozhunDOS=(List<ShopBiaozhunDO>)excelResponse.getData();
            if (shopBiaozhunDOS == null || shopBiaozhunDOS.size() == 0) {
                return excelResponse;
            }
            //数据集合添加对象
            if (shopBiaozhunMapper.batchInsert(shopBiaozhunDOS)) {
                excelResponse.setData(null);
                return excelResponse;
            } else {
                excelResponse.setErrno(0);
                excelResponse.setErrmsg("导入失败请联系管理员");
                return excelResponse;
            }
        }
    }

    public  ExcelResponse ImportExcel(MultipartFile file,UserShopDO userShopDO) {
        List<ShopBiaozhunDO> list = new ArrayList<>();
        ShopBiaozhunDO entity = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //设置时间格式
        String EXCEL_XLS = "xls";
        String EXCEL_XLSX = "xlsx";
        ExcelResponse excelResponse=new ExcelResponse();
        int hang=0;
        int lie=0;
        try {
           /* Sheet sheet = null;
            Row row = null;
            if (file.getOriginalFilename().endsWith(EXCEL_XLS)) {
                //用于xls文件的读取
                HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
                sheet = wb.getSheetAt(0);
            } else if (file.getOriginalFilename().endsWith(EXCEL_XLSX)) {
                //用于xlsx文件的读取
                XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
                //根据页面index 获取sheet页
                sheet = wb.getSheetAt(0);
            }
          */  HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            int numberOfSheets = workbook.getNumberOfSheets();
            //获取每个sheet对象
            for (int i = 0; i < numberOfSheets; i++){
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                int rows = sheetAt.getPhysicalNumberOfRows();
                //获取sheet对象下的每一行
                for(int j=0;j<rows;j++){
                    if(j==0 || j==1){
                        continue;//跳过第一第二行表头
                    }
                    hang=j+1;
                    HSSFRow row = sheetAt.getRow(j);
                    if(row == null){
                        continue;//跳过空行
                    }
                    int cells = row.getPhysicalNumberOfCells();
                    if(cells<2){//代表属性小于2个有问题过滤掉
                        continue;//跳过空行
                    }
                    //获取每一行元素数量
                    entity = new ShopBiaozhunDO();
                    entity.setGmtCreate(new Date());
                    entity.setGmtUpdate(new Date());
                    if(userShopDO!=null && userShopDO.getId().intValue()>0){
                        entity.setShopId(userShopDO.getId());
                        entity.setShopName(userShopDO.getShopName());
                    }
                    for(int k=0;k<cells;k++){
                        HSSFCell cell = row.getCell(k);
                        lie=k+1;

                        switch (k){

                            //按照对应列set对象属性
                            case 0:
                                if(hang==270 && lie==1){
                                    System.out.println("sssss");
                                }
                                if(cell!=null){
                                    cell.setCellType(CellType.STRING);
                                    entity.setGongchengXuhao(replaceBlank(cell.getStringCellValue()));
                                    if(cell.getStringCellValue().contains(".")){
                                        // String pxuhao= cell.getStringCellValue().split("\\.")[0];
                                        int endinfo=cell.getStringCellValue().lastIndexOf(".");
                                        String pxuhao=cell.getStringCellValue().substring(0,endinfo);
                                        entity.setGongchengPxuhao(pxuhao);
                                    }else{
                                        entity.setGongchengPxuhao("0");
                                    }
                                }

                                break;
                            case 1:
                                cell.setCellType(CellType.STRING);
                                entity.setGongchengCode(cell.getStringCellValue());
                                break;
                            case 2:
                                cell.setCellType(CellType.STRING);
                                entity.setGongchengName(cell.getStringCellValue());
                                break;
                            case 3:

                                if(cell!=null){
                                    cell.setCellType(CellType.STRING);
                                    entity.setDesc(cell.getStringCellValue());
                                }

                                break;
                            case 4:
                                if(cell!=null) {
                                    cell.setCellType(CellType.STRING);
                                    entity.setBunit(cell.getStringCellValue());
                                }
                                break;
                            case 5:
                                if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
                                    entity.setGongcheng_count((float) cell.getNumericCellValue());
                                }else{
                                    String gongchengCount=replaceBlank(cell.getStringCellValue().trim());
                                    if(!StringUtils.isEmpty(gongchengCount)){
                                        entity.setGongcheng_count(Float.valueOf(gongchengCount));
                                    }
                                }
                                break;
                            case 6:
                                if(cell.getCellType()==CellType.BLANK){
                                    continue;
                                }else{
                                    if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
                                        entity.setGongcheng_price((float) cell.getNumericCellValue());
                                    }else{
                                        String gongchengPrice=replaceBlank(cell.getStringCellValue().trim());
                                        if(!StringUtils.isEmpty(gongchengPrice)){
                                            entity.setGongcheng_price(Float.valueOf(gongchengPrice));
                                        }
                                     }
                                }

                                break;
                            case 7:
                                if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
                                    entity.setGongcheng_allprice((float) cell.getNumericCellValue());
                                }else{
                                    String gongchengAllPrice=replaceBlank(cell.getStringCellValue().trim());
                                    if(!StringUtils.isEmpty(gongchengAllPrice)){
                                        entity.setGongcheng_allprice(Float.valueOf(gongchengAllPrice));
                                    }
                                 }
                                 break;
                        }
                    }
                    list.add(entity);

                }
            }
            excelResponse.setErrno(1);//0代表异常
            excelResponse.setData(list);
        } catch (Exception e) {
            excelResponse.setErrno(0);//0代表异常
            excelResponse.setErrmsg("第"+hang+"行，第"+lie+"列错误请修正");
            System.out.println("行列错误hang="+hang+">>lie=="+lie);
            e.printStackTrace();
        }
        return excelResponse;
    }
    /*
     * 去除数据的空格、回车、换行符、制表符
     */
    public static String replaceBlank(String str) {
        String dest = "";
        if (str!=null) {
            //空格\t、回车\n、换行符\r、制表符\t
            Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(str);
            dest = m.replaceAll("");
        }
        return dest;
    }
    public  List<ShopBiaozhunDO> ImportExcelOld(MultipartFile file,Long shopId) {
        List<ShopBiaozhunDO> list = new ArrayList<>();
        ShopBiaozhunDO entity = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //设置时间格式
        String EXCEL_XLS = "xls";
        String EXCEL_XLSX = "xlsx";

        try {
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            int numberOfSheets = workbook.getNumberOfSheets();
            //获取每个sheet对象
            for (int i = 0; i < numberOfSheets; i++){
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                int rows = sheetAt.getPhysicalNumberOfRows();
                //获取sheet对象下的每一行
                for(int j=0;j<rows;j++){
                    if(j==0 || j==1){
                        continue;//跳过第一行表头
                    }
                    HSSFRow row = sheetAt.getRow(j);
                    if(row == null){
                        continue;//跳过空行
                    }
                    int cells = row.getPhysicalNumberOfCells();
                    //获取每一行元素数量
                    entity = new ShopBiaozhunDO();
                    if(shopId!=null){
                        entity.setShopId(shopId);
                    }
                    for(int k=0;k<cells;k++){
                        HSSFCell cell = row.getCell(k);
                        switch (k){
                            //按照对应列set对象属性

                            case 1:
                                cell.setCellType(CellType.STRING);
                                entity.setGongchengName(cell.getStringCellValue());
                                break;
                            case 2:
                                cell.setCellType(CellType.STRING);
                                entity.setGongchengCode(cell.getStringCellValue());
                                break;
                           case 3:
                               cell.setCellType(CellType.STRING);
                               entity.setBunit(cell.getStringCellValue());
                                break;
                            case 4:
                                if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
                                    entity.setGongcheng_count((float) cell.getNumericCellValue());
                                }else{
                                    entity.setGongcheng_count(Float.valueOf(cell.getStringCellValue()));
                                }
                                 break;
                            case 5:
                                if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
                                    entity.setGongcheng_price((float) cell.getNumericCellValue());
                                }else{
                                    entity.setGongcheng_price(Float.valueOf(cell.getStringCellValue()));
                                }
                                break;
                            case 7:
                                entity.setShopName( cell.getStringCellValue());
                                break;
                        }
                    }
                    list.add(entity);

                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @PostMapping("/batchCailiaoUpload")
    @ResponseBody
    public ExcelResponse batchCailiaoUpload(
            HttpServletRequest request,
            HttpServletResponse response,
            @RequestParam("file") MultipartFile file){
        String shopIdString=request.getParameter("shopId");
        Long shopId=null;
        if(shopIdString!=null && !shopIdString.equals("undefined")){
            shopId=Long.valueOf(shopIdString);
        }else{
            ExcelResponse excelResponse=new ExcelResponse();
            excelResponse.setErrno(0);
            excelResponse.setErrmsg("请选择对应项目");
            return excelResponse;
        }
        ExcelResponse excelResponse=ImportCailiaoExcel( file,shopId);
        if(excelResponse.getErrno()==0){
            //代表有异常
            return excelResponse;
        }else{
            List<ShopCailiaoDO>  shopBiaozhunDOS=(List<ShopCailiaoDO>)excelResponse.getData();
            if(shopBiaozhunDOS==null || shopBiaozhunDOS.size()==0){
                return excelResponse;
            }
            //数据集合添加对象
            if(shopCailiaoMapper.batchInsert(shopBiaozhunDOS)){
                excelResponse.setData(null);
                return excelResponse;
            }else{
                excelResponse.setErrno(0);
                excelResponse.setErrmsg("导入异常请联系管理员");
                return excelResponse;
            }
        }

    }
    public ExcelResponse ImportCailiaoExcel(MultipartFile file, Long shopId) {
        List<ShopCailiaoDO> list = new ArrayList<>();
        ShopCailiaoDO entity = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        ExcelResponse excelResponse=new ExcelResponse();
        excelResponse.setErrno(0);//0代表异常
        //设置时间格式
        String EXCEL_XLS = "xls";
        String EXCEL_XLSX = "xlsx";
        int hang=0;
        int lie=0;
        try {
           /* Sheet sheet = null;
            Row row = null;
            if (file.getOriginalFilename().endsWith(EXCEL_XLS)) {
                //用于xls文件的读取
                HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
                sheet = wb.getSheetAt(0);
            } else if (file.getOriginalFilename().endsWith(EXCEL_XLSX)) {
                //用于xlsx文件的读取
                XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
                //根据页面index 获取sheet页
                sheet = wb.getSheetAt(0);
            }
          */  HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            int numberOfSheets = workbook.getNumberOfSheets();
            //获取每个sheet对象
            for (int i = 0; i < numberOfSheets; i++){
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                int rows = sheetAt.getPhysicalNumberOfRows();
                //获取sheet对象下的每一行
                for(int j=0;j<rows;j++){
                    if(j==0 || j==1){
                        continue;//跳过第一行表头
                    }
                    hang=j+1;
                    HSSFRow row = sheetAt.getRow(j);
                    if(row == null){
                        continue;//跳过空行
                    }
                    int cells = row.getPhysicalNumberOfCells();
                    //获取每一行元素数量
                    entity = new ShopCailiaoDO();
                    entity.setGmtCreate(new Date());
                    entity.setGmtUpdate(new Date());
                    if(shopId!=null){
                        entity.setShopId(shopId);
                    }
                    for(int k=0;k<cells;k++){
                        HSSFCell cell = row.getCell(k);
                        lie=k+1;
                        switch (k){
                            //按照对应列set对象属性

                            case 1:
                                cell.setCellType(CellType.STRING);
                                entity.setGongchengName(cell.getStringCellValue());
                                break;
                            case 2:
                                cell.setCellType(CellType.STRING);
                                entity.setGongchengPinpai(cell.getStringCellValue());
                                break;
                            case 3:
                                cell.setCellType(CellType.STRING);
                                entity.setGongchengGuige(cell.getStringCellValue());
                                break;
                            case 4:
                                cell.setCellType(CellType.STRING);
                                entity.setBunit(cell.getStringCellValue());
                                break;
                            case 5:
                                if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
                                    entity.setGongcheng_count((float) cell.getNumericCellValue());
                                }else{
                                    entity.setGongcheng_count(Float.valueOf(cell.getStringCellValue()));
                                }
                                break;
                            case 6:
                                if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
                                    entity.setGongcheng_price((float) cell.getNumericCellValue());
                                }else{
                                    entity.setGongcheng_price(Float.valueOf(cell.getStringCellValue()));
                                }
                                break;
                            case 8:
                                entity.setShopName( cell.getStringCellValue());
                                break;
                        }
                    }
                    list.add(entity);

                }
            }
            excelResponse.setErrno(1);//0代表异常
            excelResponse.setData(list);
        } catch (IOException e) {
            excelResponse.setErrno(0);//0代表异常
            excelResponse.setErrmsg("第"+"行，第"+lie+"列错误请修正");
            System.out.println("行列错误hang="+hang+">>lie=="+lie);
            e.printStackTrace();
        }
        return excelResponse;
    }


    @GetMapping("/getAllCailiao")
    @ResponseBody
    public void getAllCailiao(
            HttpServletRequest request,
            HttpServletResponse response,
            @RequestParam(required = false, value = "shopId") Long shopId,
            @RequestParam(required = false, value = "startStr") String startStr,
            @RequestParam(required = false, value = "endStr") String endStr
    ){
        try {

            UserShopDO userShopDO=userShopMapper.selectById(shopId);
            response.reset();
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setContentType("application/octet-stream;charset=UTF-8");
            //设置导出的文件名
            final String extraName = userShopDO.getShopName()+"项目材料汇总表.xls";
            StringBuffer fileName = new StringBuffer(extraName);
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode(fileName.toString(), "UTF-8"));
            Wrapper<ShopCailiaoDO> wrapper = new EntityWrapper<ShopCailiaoDO>();
            if(shopId!=null){
                wrapper.eq("shop_id",shopId);
            }
            if(org.apache.commons.lang3.StringUtils.isNotEmpty(startStr)){
                Date start=new Date();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                try {
                    start = dateFormat.parse(startStr);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                wrapper.ge("gmt_update",start);
            }
            if(org.apache.commons.lang3.StringUtils.isNotEmpty(endStr)){
                Date end=new Date();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                try {
                    end = dateFormat.parse(endStr);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                wrapper.le("gmt_update",end);
            }
            List<CailiaoExcelDTO> plantGoodExcelDTOs = new ArrayList<>();

            List<ShopCailiaoDO> shopBiaozhunDOS = shopCailiaoMapper.selectList(wrapper);
            for (ShopCailiaoDO shopBiaozhunDO: shopBiaozhunDOS
            ){
                CailiaoExcelDTO dto = new CailiaoExcelDTO();
                dto.setGongchengName(shopBiaozhunDO.getGongchengName());
                dto.setGongchengPinpai(shopBiaozhunDO.getGongchengPinpai());
                dto.setGongchengGuige(shopBiaozhunDO.getGongchengGuige());
                dto.setFinishTime(shopBiaozhunDO.getFinishTime());
                dto.setFinishValue(shopBiaozhunDO.getFinishValue());
                dto.setBunit(shopBiaozhunDO.getBunit());
                dto.setGongcheng_price(shopBiaozhunDO.getGongcheng_price());
                dto.setGongcheng_allprice(shopBiaozhunDO.getGongcheng_price()*shopBiaozhunDO.getGongcheng_count());
                dto.setGongcheng_count(shopBiaozhunDO.getGongcheng_count()+"");
                dto.setJiedianName(shopBiaozhunDO.getJiedianName());
                dto.setShopName(shopBiaozhunDO.getShopName());
                plantGoodExcelDTOs.add(dto);
            }

            //设置下载的响应头
            ExcelExportService excelExportService = new ExcelExportService();
            Workbook workbook = new HSSFWorkbook();
            ExportParams exportParams = new ExportParams();
            final String sheetName = startStr + " - " + endStr+"材料信息";
            StringBuffer title = new StringBuffer(sheetName);
            exportParams.setSheetName(sheetName);

            ExportParams userDistributionExportParams = new ExportParams();
            userDistributionExportParams.setTitle(title.toString());
            userDistributionExportParams.setSheetName(title.toString());
            excelExportService.createSheet(workbook, userDistributionExportParams, CailiaoExcelDTO.class, plantGoodExcelDTOs);
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
